using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Diagnostics;
using FirebirdSql.Data.Firebird;

namespace create_database
{
	public class MainForm : System.Windows.Forms.Form
	{
		#region - Controls -
		private System.Windows.Forms.MainMenu mainMenu;
		private System.Windows.Forms.MenuItem menuFileExecuteScript;
		private System.Windows.Forms.OpenFileDialog openFileDialog;
		private System.Windows.Forms.MenuItem menuDatabase;
		private System.Windows.Forms.SaveFileDialog saveFileDialog;
		private System.Windows.Forms.MenuItem menuScript;
		private System.Windows.Forms.MenuItem menuScriptRun;
		private System.Windows.Forms.MenuItem menuDatabaseConnect;
		private System.Windows.Forms.MenuItem menuDatabaseDisconnect;
		private System.Windows.Forms.MenuItem menuDatabaseCreate;
		private System.Windows.Forms.MenuItem menuDatabaseSelect;
		private System.Windows.Forms.MenuItem menuDatabaseDrop;
		/// <summary>
		/// Required designer variable.
		/// </summary>
		private System.ComponentModel.Container components = null;

		public MainForm()
		{
			InitializeComponent();
		}
		#endregion - Controls -

		private string hostname = "localhost";
        private string databaseFileName = "c:\\data\\demo-db.fdb";
		private string scriptFileName = "c:\\usp\\ultraschall\\tutorials\\firebird-sql\\prj\\create-database\\create-scripts\\create-customer.sql";
		FirebirdSql.Data.Firebird.FbConnectionStringBuilder dbconnectionStringBuilder;
		FirebirdSql.Data.Firebird.FbConnection dbconnection;
		FirebirdSql.Data.Firebird.Isql.FbScript dbscript;
		FirebirdSql.Data.Firebird.FbCommand dbcommand;

		#region - Generated Code -
		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
			this.mainMenu = new System.Windows.Forms.MainMenu();
			this.menuDatabase = new System.Windows.Forms.MenuItem();
			this.menuDatabaseSelect = new System.Windows.Forms.MenuItem();
			this.menuDatabaseCreate = new System.Windows.Forms.MenuItem();
			this.menuDatabaseConnect = new System.Windows.Forms.MenuItem();
			this.menuDatabaseDisconnect = new System.Windows.Forms.MenuItem();
			this.menuScript = new System.Windows.Forms.MenuItem();
			this.menuFileExecuteScript = new System.Windows.Forms.MenuItem();
			this.menuScriptRun = new System.Windows.Forms.MenuItem();
			this.openFileDialog = new System.Windows.Forms.OpenFileDialog();
			this.saveFileDialog = new System.Windows.Forms.SaveFileDialog();
			this.menuDatabaseDrop = new System.Windows.Forms.MenuItem();
			// 
			// mainMenu
			// 
			this.mainMenu.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
																					 this.menuDatabase,
																					 this.menuScript});
			// 
			// menuDatabase
			// 
			this.menuDatabase.Index = 0;
			this.menuDatabase.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
																						 this.menuDatabaseSelect,
																						 this.menuDatabaseCreate,
																						 this.menuDatabaseConnect,
																						 this.menuDatabaseDisconnect,
																						 this.menuDatabaseDrop});
			this.menuDatabase.Text = "&Database";
			// 
			// menuDatabaseSelect
			// 
			this.menuDatabaseSelect.Index = 0;
			this.menuDatabaseSelect.Text = "&Select";
			this.menuDatabaseSelect.Click += new System.EventHandler(this.menuDatabaseSelect_Click);
			// 
			// menuDatabaseCreate
			// 
			this.menuDatabaseCreate.Index = 1;
			this.menuDatabaseCreate.Text = "C&reate";
			this.menuDatabaseCreate.Click += new System.EventHandler(this.menuDatabaseCreate_Click);
			// 
			// menuDatabaseConnect
			// 
			this.menuDatabaseConnect.Index = 2;
			this.menuDatabaseConnect.Text = "&Connect";
			this.menuDatabaseConnect.Click += new System.EventHandler(this.menuDatabaseConnect_Click);
			// 
			// menuDatabaseDisconnect
			// 
			this.menuDatabaseDisconnect.Index = 3;
			this.menuDatabaseDisconnect.Text = "&Disconnect";
			this.menuDatabaseDisconnect.Click += new System.EventHandler(this.menuDatabaseDisconnect_Click);
			// 
			// menuScript
			// 
			this.menuScript.Index = 1;
			this.menuScript.MenuItems.AddRange(new System.Windows.Forms.MenuItem[] {
																					   this.menuFileExecuteScript,
																					   this.menuScriptRun});
			this.menuScript.Text = "&Script";
			// 
			// menuFileExecuteScript
			// 
			this.menuFileExecuteScript.Index = 0;
			this.menuFileExecuteScript.Text = "&Select...";
			this.menuFileExecuteScript.Click += new System.EventHandler(this.menuScriptSelectScript_Click);
			// 
			// menuScriptRun
			// 
			this.menuScriptRun.Index = 1;
			this.menuScriptRun.Text = "&Run";
			this.menuScriptRun.Click += new System.EventHandler(this.menuScriptRun_Click);
			// 
			// openFileDialog
			// 
			this.openFileDialog.DefaultExt = "*.sql";
			this.openFileDialog.Filter = "SQL scripts (*.sql)|*.sql|All files (*.*)|*.*";
			this.openFileDialog.Title = "Select SQL Script to Run";
			// 
			// saveFileDialog
			// 
			this.saveFileDialog.DefaultExt = "*.fdb";
			this.saveFileDialog.FileName = "demo-db.fdb";
			this.saveFileDialog.Title = "Create Database";
			// 
			// menuDatabaseDrop
			// 
			this.menuDatabaseDrop.Index = 4;
			this.menuDatabaseDrop.Text = "Dro&p";
			this.menuDatabaseDrop.Click += new System.EventHandler(this.menuDatabaseDrop_Click);
			// 
			// MainForm
			// 
			this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
			this.ClientSize = new System.Drawing.Size(292, 273);
			this.Menu = this.mainMenu;
			this.Name = "MainForm";
			this.Text = "Firebird SQL Demo";

		}
		#endregion

		[STAThread]
		static void Main() 
		{
			Application.Run(new MainForm( ));
		}
		#endregion - Generated Code -

		private void menuDatabaseSelect_Click(object sender, System.EventArgs e)
		{
			this.saveFileDialog.FileName = this.databaseFileName;
			if ( this.saveFileDialog.ShowDialog ( this ) == System.Windows.Forms.DialogResult.OK )
			{
				this.databaseFileName = this.saveFileDialog.FileName;
			}
		}
		private void menuDatabaseCreate_Click(object sender, System.EventArgs e)
		{
			this.CreateDatabase( );
		}

		private void menuDatabaseDrop_Click(object sender, System.EventArgs e)
		{
			this.DropDatabase( );
		}

		private void menuScriptSelectScript_Click(object sender, System.EventArgs e)
		{
			try
			{
				if ( this.openFileDialog.ShowDialog ( this ) == DialogResult.OK )
				{ 
					this.scriptFileName = this.openFileDialog.FileName;
					Trace.WriteLine( string.Format( "Script file selected: {0}", this.scriptFileName ));
				}
			}
			catch ( System.Exception ex )
			{
				Trace.WriteLine ( ExceptionFormatter.Format( ex ), "Error" );
			}
		}

		private void menuDatabaseConnect_Click(object sender, System.EventArgs e)
		{
			this.Connect ( );
		}

		private void menuScriptRun_Click(object sender, System.EventArgs e)
		{
			this.RunScript( );		
		}

		private void menuDatabaseDisconnect_Click(object sender, System.EventArgs e)
		{
			this.Disconnect ( );
		}
		

		protected void CreateConnectionStringBuilder ( )
		{
			Trace.WriteLine( "CreateConnectionStringBuilder", "Enter" );
			Trace.Indent ( );
			try
			{
				this.dbconnectionStringBuilder = new FbConnectionStringBuilder( );
				dbconnectionStringBuilder.DataSource = this.hostname;
				dbconnectionStringBuilder.Port = 3050;
				dbconnectionStringBuilder.Dialect = 3;
				dbconnectionStringBuilder.Database = this.databaseFileName;
				dbconnectionStringBuilder.UserID = "SYSDBA";
				dbconnectionStringBuilder.Password = "masterkey";
				Trace.WriteLine( this.dbconnectionStringBuilder.ConnectionString, "Info" );
			}
			catch ( System.Exception ex )
			{
				Trace.WriteLine ( ExceptionFormatter.Format( ex ), "Error" );
			}
			finally
			{
				Trace.Unindent ( );
				Trace.WriteLine( "CreateConnectionStringBuilder", "Exit" );
			}
		}
		
		protected void CreateDatabase ( )
		{
			Trace.WriteLine( "CreateDatabase", "Enter" );
			Trace.Indent ( );
			try
			{
				if ( this.dbconnectionStringBuilder == null ) this.CreateConnectionStringBuilder( );
				dbconnectionStringBuilder.Charset = "UTF8";
				FirebirdSql.Data.Firebird.FbConnection.CreateDatabase( this.dbconnectionStringBuilder.ConnectionString, 1024, true, true );
				System.IO.File.Move( this.databaseFileName, this.databaseFileName );
				Trace.WriteLine( "Database created successfully." );
			}
			catch ( System.Exception ex )
			{
				Trace.WriteLine ( ExceptionFormatter.Format( ex ), "Error" );
			}
			finally
			{
				Trace.Unindent ( );
				Trace.WriteLine( "CreateDatabase", "Exit" );
			}
		}

		protected void DropDatabase ( )
		{
			Trace.WriteLine( "DropDatabase", "Enter" );
			Trace.Indent ( );
			try
			{
				Trace.WriteLine( "Database: {0}", this.databaseFileName );
				if ( this.dbconnectionStringBuilder == null ) this.CreateConnectionStringBuilder( );
				FirebirdSql.Data.Firebird.FbConnection.DropDatabase( dbconnectionStringBuilder.ConnectionString  );
				Trace.WriteLine( "Database dropped successfully." );
			}
			catch ( System.Exception ex )
			{
				Trace.WriteLine ( ExceptionFormatter.Format( ex ), "Error" );
			}
			finally
			{
				Trace.Unindent ( );
				Trace.WriteLine( "DropDatabase", "Exit" );
			}
		}

		protected void Connect ( )
		{
			Trace.WriteLine( "Connect", "Enter" );
			Trace.Indent ( );
			try
			{
				Trace.WriteLine( string.Format( "Database: {0}", this.databaseFileName ));
				if ( this.dbconnectionStringBuilder == null ) this.CreateConnectionStringBuilder( );
				dbconnectionStringBuilder.ConnectionLifeTime = 2500;
				this.dbconnection = new FirebirdSql.Data.Firebird.FbConnection ( this.dbconnectionStringBuilder.ConnectionString );
				this.dbconnection.Open( );
				Trace.WriteLine( "Database connected successfully." );
			}
			catch ( System.Exception ex )
			{
				Trace.WriteLine ( ExceptionFormatter.Format( ex ), "Error" );
			}
			finally
			{
				Trace.Unindent ( );
				Trace.WriteLine( "Connect", "Exit" );
			}
		}

		protected void Disconnect ( )
		{
			Trace.WriteLine( "Disconnect", "Enter" );
			Trace.Indent ( );
			try
			{
				this.dbconnection.Close ( );
				Trace.WriteLine( "Database disconnected successfully.", "Exit" );
			}
			catch ( System.Exception ex )
			{
				Trace.WriteLine( ExceptionFormatter.Format( ex ), "Error" );
			}
			finally
			{
				Trace.Unindent ( );
				Trace.WriteLine( "Disconnect", "Exit" );
			}
		}

		protected void RunScript ( )
		{
			Trace.WriteLine( "ExecuteScript", "Enter" );
			Trace.Indent ( );
			try
			{
				Trace.WriteLine( string.Format( "File: {0}", this.scriptFileName ), "Info" );
				Trace.WriteLine( "Parsing selected script", "Info" );
				this.dbscript = new FirebirdSql.Data.Firebird.Isql.FbScript( this.scriptFileName );
				int result = this.dbscript.Parse( );
				Trace.WriteLine( string.Format( "Parsed {0} commands.", result ));
				Trace.WriteLine( "Creating command object.", "Info" );
				this.dbcommand = this.dbconnection.CreateCommand( );
				this.dbcommand.CommandType = System.Data.CommandType.Text;
				Trace.WriteLine( "Executing script.", "Info" );
				foreach ( string cmd in this.dbscript.Results )
				{
					try
					{
						Trace.WriteLine( string.Format( "Command text: {0}", ExceptionFormatter.Format( cmd )));
						this.dbcommand.CommandText = cmd;
						result = this.dbcommand.ExecuteNonQuery( );
						Trace.WriteLine( string.Format( "Rows affected: {0}", result == -1 ? "n/a" : result.ToString() ), result == 0 ? "Error" : "Info" );
					}
					catch ( FirebirdSql.Data.Firebird.FbException ex )
					{
						// Token unknown
						Trace.WriteLine ( ExceptionFormatter.Format( ex ), "Ignored" );
						if ( ex.ErrorCode == -104 ) continue;
					}
				}
				Trace.WriteLine( "Script executed successfully.", "Info" );
			}
			catch ( System.Exception ex )
			{
				Trace.WriteLine ( ExceptionFormatter.Format( ex ), "Error" );
			}
			finally
			{
				Trace.Unindent ( );
				Trace.WriteLine( "ExecuteScript", "Exit" );
			}
		}
	}
}
